﻿CREATE PROCEDURE [dbo].[Forum_Section_Add]
	@parentId SMALLINT,
	@hashTag NVARCHAR(128),
	@name NVARCHAR(MAX),
	@currentId SMALLINT = NULL OUT
AS
BEGIN
	DECLARE 
		@level TINYINT
		,@right_key BIGINT

	SELECT @level = [Level], @right_key = [RightKey]
	FROM [dbo].[forum_Section]
	WHERE [SectionId] = @parentId


	BEGIN TRAN

		UPDATE [dbo].[forum_Section]
		SET [LeftKey] = CASE WHEN [LeftKey] > @right_key 
											THEN [LeftKey] + 2 
											ELSE [LeftKey] END, 
				[RightKey] = CASE WHEN [RightKey] >= @right_key 
											THEN [RightKey] + 2 
											ELSE [RightKey] END 
		WHERE [RightKey] >= @right_key 

		IF @currentId IS NULL
		BEGIN
			SELECT @currentId = MAX([SectionId]) + 1 FROM [dbo].[forum_Section]
		END

		SET @hashTag = LTRIM(RTRIM(LOWER(@hashTag)))

		SET IDENTITY_INSERT [dbo].[forum_Section] ON 
		INSERT INTO [dbo].[forum_Section]
							 ([SectionId]
							 ,[ParentSectionId]
							 ,[LeftKey]
							 ,[RightKey]
							 ,[Level]
							 ,[HashTag]
							 ,[Name])
			 VALUES
						 (@currentId
						 ,@parentId
						 ,@right_key --<LeftKey, bigint,>
						 ,@right_key + 1 --<RightKey, bigint,>
						 ,@level + 1 --<Level, tinyint,>
						 ,@hashTag --<HashTag, nvarchar(128),>
						 ,@name)
		SET IDENTITY_INSERT [dbo].[text_BookTree] OFF 

	COMMIT TRAN
	RETURN 0
END